/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import control.Start;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Emprestimo;
import model.backstage.Data;

/**
 *
 * @author Guilherme
 */
public class DataEmprestimo
{
    public static boolean add(Emprestimo e)
    {
        String s = "('" + e.getItemID() + "', '" + e.getUsuarioID() + "', '" + e.getDataEmprestimo() + "', '" + e.getDataDevolucao() +
                "', " + e.getNroRenovacoes() + ")";

        return Link.add("emprestimo", s);
    }
    
    public static boolean delete(Emprestimo e)
    {
        String str = "itemID = '" + e.getItemID() + "' AND usuarioID = '" + e.getUsuarioID() +"'";
        return Link.delete("emprestimo", str);
    }
    
    public static boolean renova(Emprestimo e)
    {
        e.getDataDevolucao().soma7();
        String set = e.getDataDevolucao().toString();
        String where = "itemID = " + e.getItemID() + " AND usuarioID = " + e.getUsuarioID();
        return Link.update("emprestimo", set, where);
    }
    /**
     * 
     * @param r itemID
     * @param s usuarioID
     * @return 
     */
    public static Emprestimo select (String r, String s)
    {
        ResultSet rs = Link.select("emprestimo", "*", "itemID = '" + r + "' AND usuarioID = '" + s + "'");
        Emprestimo e = null;
        try
        {
            while (rs.next())
                   {
                       Data d1 = new Data (rs.getString("dataEmprestimo"));
                       Data d2 = new Data (rs.getString("dataDevolucao"));
                       e = new Emprestimo (rs.getString("itemID"), rs.getString("usuarioID"), 
                               d1, d2);
                   }
        } catch (SQLException ex)
        {
            Logger.getLogger(Start.class.getName()).log(Level.SEVERE, null, ex);
        }
        return e;
    }
    
    public static ResultSet lista ()
    {
        String s = "(SELECT DISTINCT e.usuarioID, u.pnome "
                + "FROM emprestimo e JOIN usuario u ON u.usuarioID = e.usuarioID) a "
                + "JOIN"
                + "(SELECT e.usuarioID, e.itemID, i.titulo "
                + "FROM emprestimo e JOIN item i ON i.itemID = e.itemID) b "
                + "ON a.usuarioID = b.usuarioID ";
        return Link.select(s, "a.usuarioID, a.pnome, b.titulo, b.itemID", "true");
    }
}

/*CREATE TABLE emprestimo (
	itemID VARCHAR(20) NOT NULL,
	usuarioID VARCHAR(10) NOT NULL,
	dataEmprestimo date NOT NULL,
	dataDevolucao date NOT NULL,
	nroRenovacoes int,
	PRIMARY KEY (itemID, usuarioID)
);*/